package locacao.integracao.dao.pstPostgreSQL;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;

import locacao.integracao.dao.LocacaoDAO;
import locacao.negocio.to.LocacaoTO;
import locacao.util.LocacaoUtil;

public class LocacaoPgsqlDAO implements LocacaoDAO {

    private ArrayList<LocacaoTO> locacoes;

    public LocacaoPgsqlDAO(){
	   setLocacoes(new ArrayList<LocacaoTO>());
    }


    public void addOrUpdateLocacao(LocacaoTO locacao) {
    	Connection conn = null;
    	CallableStatement spManterLocacao = null;

    	try {        	
          String sql = "{call locacao.manter_locacao(?,?,?,?,?,?)}";
          conn = ConnectionRentACar.getInstance().getConnection();
          
          spManterLocacao = conn.prepareCall(sql);
          
          spManterLocacao.setInt(1, locacao.getIdLocacao());
          spManterLocacao.setInt(2, locacao.getIdFuncionario());
          spManterLocacao.setInt(3, locacao.getIdCliente());
          spManterLocacao.setDate(4, (Date) locacao.getDataRetirada());
          spManterLocacao.setDate(5, (Date) locacao.getDataDevolucao());
          spManterLocacao.setString(6, locacao.getObservacao());
          
          spManterLocacao.execute();
        } catch (Exception e) {
          System.out.println(e.getMessage());
        } finally {
          try {
            if (spManterLocacao != null)
            	spManterLocacao.close();

          } catch (SQLException e) {
            System.out.println(e.getMessage());
          }
        }
    }

    /**
     *

    /**
     * @param locacao
     */
    public void excludeLocacao( LocacaoTO locacao )  {
    	Connection conn = null;
    	CallableStatement spDeleteLocacao = null;

    	try {        	
          String sql = "{call locacao.delete_locacao(?)}";
          conn = ConnectionRentACar.getInstance().getConnection();
          
          spDeleteLocacao = conn.prepareCall(sql);
          
          spDeleteLocacao.setInt(1, locacao.getIdLocacao());         
          
          spDeleteLocacao.execute();
        } catch (Exception e) {
          System.out.println(e.getMessage());
        } finally {
          try {
            if (spDeleteLocacao != null)
            	spDeleteLocacao.close();

          } catch (SQLException e) {
            System.out.println(e.getMessage());
          }
        }
    }


  
    /**
     * @return
     */
    public String[] findLocacaoById(Integer id){
    	 Connection conn = null;
    	 Statement stmt = null;
    	 ResultSet rSet = null;
    	 String[] info = null;

    	 String sql = "SELECT  idlocacao, idfuncionario, idcliente, dtretirada, dtdevolucao, observacao ";
    	 sql += " FROM locacao.locacao WHERE idlocacao =  " + id;

    	 try {
    		 conn = ConnectionRentACar.getInstance().getConnection();
    		 stmt = conn.createStatement();
    		 rSet = stmt.executeQuery(sql);

    		 if (rSet != null) {    			 
    			 while (rSet.next()) {
    				 
    				info = new String[locacoes.size()];
    	    		   info[0] = rSet.getInt("idlocacao") 
    	       		   + " - " + rSet.getInt("idfuncionario")
    	       		   + " - " + rSet.getInt("idcliente")
    	       		   + " - " + rSet.getDate("dtretirada")
    	       		   + " - " + rSet.getDate("dtdevolucao")
    	       		   + " - " + rSet.getString("observacao");    		   
    			 }    			 
    		 }
			 return info;
			 
    	 } catch (Exception e) {
    		 System.out.println(e.getMessage());
    		 return null;
    	 } finally {
    		 try {
    			 if (rSet != null)
    				 rSet.close();
    			 if (stmt != null)
    				 stmt.close();
    		 } catch (SQLException e) {
    			 System.out.println(e.getMessage());
    		 }
    	 }
    }

    
    /**
     * @return
     */
    public String[] getInfoLocacao()
    {
        LocacaoTO locacao;
        locacoes = getLocacoes();
        String[] info = new String[locacoes.size()];
        Iterator iteratorLocacaos;
        iteratorLocacaos = locacoes.iterator();
        int i=0;
        while ( iteratorLocacaos.hasNext() )
        {
            locacao = (LocacaoTO) iteratorLocacaos.next();
            info[i] = locacao.getIdLocacao()             		
            		+ " - " + LocacaoUtil.formataDataStr(locacao.getDataRetirada())
            		+ " - " + LocacaoUtil.formataDataStr(locacao.getDataDevolucao())
            		+ " - " + locacao.getObservacao()
            		+ " - " + locacao.getIdCliente()
            		+ " - " + locacao.getIdFuncionario()   ;
            i++;
        }
        return info;
    }


    /**
     * @return
     */
    public ArrayList<LocacaoTO> getLocacoes() {
    	 Connection conn = null;
    	 Statement stmt = null;
    	 ResultSet rSet = null;
    	 LocacaoTO locacao = null;

    	 String sql = "SELECT  idlocacao, idfuncionario, idcliente, dtretirada, dtdevolucao, observacao ";
    	 sql += " FROM locacao.locacao ORDER BY idlocacao";

    	 try {
    		 conn = ConnectionRentACar.getInstance().getConnection();
    		 stmt = conn.createStatement();
    		 rSet = stmt.executeQuery(sql);

    		 if (rSet != null) {
    			 locacoes = new ArrayList();
    			 while (rSet.next()) {
    				 
    				 locacao = new LocacaoTO();
    				 
    				 locacao.setIdLocacao(rSet.getInt("idlocacao"));
    				 locacao.setIdFuncionario(rSet.getInt("idfuncionario"));
    				 locacao.setIdCliente(rSet.getInt("idcliente"));
    				 locacao.setDataRetirada(rSet.getDate("dtretirada"));
    				 locacao.setDataDevolucao(rSet.getDate("dtdevolucao"));
    				 locacao.setObservacao(rSet.getString("observacao"));
    				 
    				 locacoes.add(locacao);
    			 }
    		 }
    	 } catch (Exception e) {
    		 System.out.println(e.getMessage());
    	 } finally {
    		 try {
    			 if (rSet != null)
    				 rSet.close();
    			 if (stmt != null)
    				 stmt.close();
    		 } catch (SQLException e) {
    			 System.out.println(e.getMessage());
    		 }
    	 }
    	 return locacoes;
    }

    private void setLocacoes(ArrayList<LocacaoTO> locacoes) {
    	this.locacoes = locacoes;
    }
    
}
